package com.wnzt.baszh.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.wnzt.baszh.dao.IArchivingDao;
import com.wnzt.baszh.util.common.ConstantsUtils;
import com.wnzt.baszh.util.common.GlobalFun;
import com.wnzt.baszh.util.common.PageHelper;
import com.wnzt.baszh.util.common.PageHelperService;
/**
 * 归档dao层实现类
 * @author dqw
 * @date 2017-5-16
 */
@Repository
public class ArchivingDaoImpl extends PageHelperService implements IArchivingDao{
	
	public static final  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	
	@Autowired
	private JdbcTemplate jdbcTemplate; 

	@Override
	public PageHelper queryArchiving(Map<String, Object> map) {
		String date1 = (String) map.get("date1");
		String date2 = (String) map.get("date2");
		String fcydept = (String) map.get("fcydept");
		String fzzDoc = (String) map.get("fzzDoc");
		String fprn = (String) map.get("fprn");
		String fname = (String) map.get("fname");
		String fcybq = (String) map.get("fcybq");
		String status = (String) map.get("status");
		String day = (String) map.get("day");
		String page = (String) map.get("page");
		String rows = (String) map.get("rows");
		StringBuffer sb = new StringBuffer(" 1=1 ");
		int pageSize = 10;
		int currentPage = 1;
		if(ConstantsUtils.isNotBank(date1)){
			sb.append("and a.fcydate >= '"+date1+"'");
		}
		if(ConstantsUtils.isNotBank(date2)){
			Date DATE = new Date();
			try {
				DATE = sdf.parse(date2);
			} catch (ParseException e) {
				e.printStackTrace();
			}
    		 Date date = new Date(DATE.getTime() + 1000 * 60 * 60 * 24);
    		 String Date = sdf.format(date);
			sb.append(" and a.fcydate < '"+Date+"'");
		}
		if(ConstantsUtils.isNotBank(fzzDoc)){
			sb.append(" and a.FZZDOCTBH = '"+ fzzDoc +"' ");
		}
		if(ConstantsUtils.isNotBank(fprn)){
			sb.append(" and a.fprn = '"+fprn+"' or a.fbarcode = '"+fprn+"' ");
		}
		if(ConstantsUtils.isNotBank(fname)){
			sb.append(" and a.fname like '%"+ fname +"'%");
		}
		if(ConstantsUtils.isNotBank(fcybq)){
			sb.append(" and a.fcybs = '"+ fcybq +"' ");
		}
		if(ConstantsUtils.isNotBank(status)){
			if(status.equals("0")){
				sb.append(" ");
			}else if(status.equals("1")){
				sb.append(" and a.status = '1'");
			}else{
				sb.append(" and a.status = '2'");
			}
		}
		if(ConstantsUtils.isNotBank(day)){
			sb.append(" a.fdays = "+day+"");
		}
		if (ConstantsUtils.isNotBank(page)) {
            currentPage = Integer.valueOf(page);
        }
        if (ConstantsUtils.isNotBank(rows)) {
            pageSize = Integer.valueOf(rows);
        }
		if(ConstantsUtils.isNotBank(fcydept)){
			 String departNumSql = "select fcal from tworkroom where ftykh='" + fcydept + "'";
	            Map<String, Object> departNumMap = this.jdbcTemplate.queryForMap(departNumSql);
	            String departNumStr = departNumMap.get("fcal").toString().replace(",", "','");
	            sb.append(" and a.fcytykh in ('" + departNumStr + "')");
		}
		String field = " a.fprn,a.fname,a.fzzdoct,a.fdays,a.fzzdoctbh,a.fcydate,a.fcydept,a.fcybs,a.fsex,a.fzyid,a.ftimes,"
				+ " a.fbarcode,b.fstatus,b.fdate,(DATEDIFF(day, a.fcydate, b.fdate ) - ( select count(fid) from TPLAYTIME where fplaydate BETWEEN  a.fcydate and  b.fdate)) as days ";
		String table = "  SZH_BASE a LEFT JOIN SZH_BAGD b on a.fzyid = b.fzyid  ";
		return this.queryMssqlSplitPageByProc(field, table, sb.toString(), "b.fstatus,a.fcydate", 1, " a.fzyid ", currentPage, pageSize);
	}

	@Override
	public List<List<Map<String, Object>>>  getArchivingByFzyid(String fzyids) {
		String[] fzyid = fzyids.split(",");
		
		List<List<Map<String, Object>>> list = new ArrayList<List<Map<String, Object>>>();
		Map<String, Object> totalMap = new HashMap<String, Object>();
		for (int i = 0; i < fzyid.length; i++) {
			List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
			String sql = "select count(a.fid) as number ,a.fzyid,b.fname from SZH_BAFP a LEFT JOIN SZH_BLLXMX b on a.ftpfl = b.fcode"
					+ " where a.fzyid = '"+ fzyid[i] +"' GROUP BY fzyid,b.fname ";
			sql+=" Union All select a.FPAGES as number ,'"+fzyid[i]+"' fzyid,a.FBLMC fname from dbo.SZH_BAGDMX a where a.FGDID in (select b.FID from dbo.SZH_BAGD b where b.FZYID='"+fzyid[i]+"')";
			resultList = this.jdbcTemplate.queryForList(sql);
			if(resultList.size() > 0){
				int sum = 0;
				//计算每份病历的归档总数
				for (Map<String, Object> map : resultList) {
					int number = (Integer) map.get("number");
					sum += number;
				}
				totalMap.put("total", sum);
				resultList.add(totalMap);
				sum = 0;
				list.add(resultList);
			}
			
		}
		return list;
	}

	@Override
	public List<Map<String, Object>> getDeptByFcode(String fcode) {
		String sql2 = "select fworkroom from tsysuser where fcode = '"+fcode+"'";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql2);
		List<Map<String, Object>> list2 = null;
		if(list.size() > 0){
			//获取对应的科室
			for (Map<String, Object> map : list) {
				String str = (String) map.get("fworkroom");
				String room = str.replace("\"", "'");
				String sql = "select ftykh,fksname from tworkroom where ftype='2' and ftykh in("+room+")  order by fpx";
				list2 = this.jdbcTemplate.queryForList(sql);
			}
		}
		return list2;
		
	}

	@Override
	public List<Map<String, Object>> getBQByFcode(String fcode) {
		String sql2 = "select fbqdm,fbqmc from tsysuser where fcode = '"+fcode+"'";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql2);
		List<Map<String, Object>> list2 = null;
		if(list.size() > 0){
			//获取对应的科室
			for (Map<String, Object> map : list) {
				String str = (String) map.get("fworkroom");
				String room = str.replace("\"", "'");
				String sql = "";
				list2 = this.jdbcTemplate.queryForList(sql);
			}
		}
		return list2;
	}
	
	@Override
	public List<Map<String, Object>> getFcybsByKsbh(String ksbh, String fcode) {
		String sql ="";
		String sql2 = "select fbqdm,fbqmc,fworkbq from tsysuser where fcode = '"+fcode+"'";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql2);
		List<Map<String, Object>> resultList = null;
		if(list.size() > 0){
			for (Map<String, Object> map : list) {
				if( map.get("fworkbq") != null){
					String str = (String) map.get("fworkbq");
					String bq = str.replace("\"", "'");
					if(ConstantsUtils.isNotBank(ksbh)){
						sql = "select  fhiskh,fhisksname from THISKSSET where FKSTYPE=81 AND FTYKH= '"+ksbh+"'  ";
					}else{
						sql = "select fhiskh,fhisksname from THISKSSET where FKSTYPE=81 and FTYKH in("+bq+")";
					}
					resultList = this.jdbcTemplate.queryForList(sql);
				}
			}
		}
		return resultList;
	}

	@Override
	public PageHelper queryAllArchiving(Map<String, Object> map) {
		String date1 = (String) map.get("date1");
		String date2 = (String) map.get("date2");
		String fcydept = (String) map.get("fcydept");
		String fzzDoc = (String) map.get("fzzDoc");
		String fprn = (String) map.get("fprn");
		String fname = (String) map.get("fname");
		String fcybq = (String) map.get("fcybq");
		String status = (String) map.get("status");
		String rqlx = (String) map.get("rqlx");
		String day = (String) map.get("day");
		String jsDay = (String) map.get("jsDay");
		String yqDay = (String) map.get("yqDay");
		String ycyDay = (String) map.get("ycyDay");
		String page = (String) map.get("page");
		String rows = (String) map.get("rows");
		StringBuffer sb = new StringBuffer(" 1=1 ");
		int pageSize = 10;
		int currentPage = 1;
		if("1".equals(rqlx)){
			if(ConstantsUtils.isNotBank(date1)){
				sb.append("and a.fcydate >= '"+date1+"'");
			}
			if(ConstantsUtils.isNotBank(date2)){
				Date DATE = new Date();
				try {
					DATE = sdf.parse(date2);
				} catch (ParseException e) {
					e.printStackTrace();
				}
	    		 Date date = new Date(DATE.getTime() + 1000 * 60 * 60 * 24);
	    		 String Date = sdf.format(date);
				sb.append(" and a.fcydate < '"+Date+"'");
			}
		}else if("2".equals(rqlx)){
			if(ConstantsUtils.isNotBank(date1)){
				sb.append("and b.fdate >= '"+date1+"'");
			}
			if(ConstantsUtils.isNotBank(date2)){
				Date DATE = new Date();
				try {
					DATE = sdf.parse(date2);
				} catch (ParseException e) {
					e.printStackTrace();
				}
	    		 Date date = new Date(DATE.getTime() + 1000 * 60 * 60 * 24);
	    		 String Date = sdf.format(date);
				sb.append(" and b.fdate < '"+Date+"'");
			}
		}
		
		if(ConstantsUtils.isNotBank(fzzDoc)){
			sb.append(" and a.FZZDOCTBH = '"+ fzzDoc +"' ");
		}
		if(ConstantsUtils.isNotBank(fprn)){
			sb.append(" and a.fprn = '"+fprn+"' or a.fbarcode = '"+fprn+"' ");
		}
		if(ConstantsUtils.isNotBank(fname)){
			sb.append(" and a.fname like '%"+ fname +"'%");
		}
		if(ConstantsUtils.isNotBank(fcybq)){
			sb.append(" and a.fcybs = '"+ fcybq +"' ");
		}
		if(ConstantsUtils.isNotBank(ycyDay)){
			sb.append(" and DATEDIFF(day, a.fcydate, GETDATE()) >= "+ycyDay+" ");
		}
		if(ConstantsUtils.isNotBank(status)){//是否归档
			if(status.equals("0")){
				sb.append(" ");
			}else if(status.equals("1")){
				sb.append(" and b.fstatus = '1'");
			}else{
				sb.append(" and (b.fstatus = '0' OR b.fstatus is null)");
			}
		}
		//(DATEDIFF(day, a.fcydate, b.fdate ) - ( select count(fid) from TPLAYTIME where fplaydate BETWEEN  a.fcydate and  b.fdate)) 归档天数
		if(ConstantsUtils.isNotBank(jsDay) && !"0".equals(jsDay)){
			sb.append(" and (DATEDIFF(day, a.fcydate, b.fdate ) - ( select count(fid) from TPLAYTIME where fplaydate BETWEEN  a.fcydate and  b.fdate))>="+jsDay);
		}
		if(ConstantsUtils.isNotBank(yqDay) && !"0".equals(yqDay)){
			sb.append(" and (DATEDIFF(day, a.fcydate, b.fdate ) - ( select count(fid) from TPLAYTIME where fplaydate BETWEEN  a.fcydate and  b.fdate))<="+yqDay);
		}
		/*if(ConstantsUtils.isNotBank(day)){//住院天数
			sb.append(" a.fdays = "+day+"");
		}*/
		if (ConstantsUtils.isNotBank(page)) {
            currentPage = Integer.valueOf(page);
        }
        if (ConstantsUtils.isNotBank(rows)) {
            pageSize = Integer.valueOf(rows);
        }
		if(ConstantsUtils.isNotBank(fcydept)){
			 String departNumSql = "select fcal from tworkroom where ftykh='" + fcydept + "'";
	            Map<String, Object> departNumMap = this.jdbcTemplate.queryForMap(departNumSql);
	            String departNumStr = departNumMap.get("fcal").toString().replace(",", "','");
	            sb.append(" and a.fcytykh in ('" + departNumStr + "')");
		}
		String field = " a.fprn,a.fname,a.fzzdoct,a.fdays,a.fzzdoctbh,a.fcydate,a.fcydept,a.fcybs,a.fsex,a.fzyid,a.ftimes,"
				+ " a.fbarcode,b.fstatus,b.fdate,(DATEDIFF(day, a.fcydate, b.fdate ) - ( select count(fid) from TPLAYTIME where fplaydate BETWEEN  a.fcydate and  b.fdate)) as days ";
		String table = "  SZH_BASE a LEFT JOIN SZH_BAGD b on a.fzyid = b.fzyid  ";
		return this.queryMssqlSplitPageByProc(field, table, sb.toString(), " a.fzyid ", 1, " a.fzyid ", currentPage, pageSize);
	}

	@Override
	public List<Map<String, Object>> getAllFcybs(String ksbh) {
		String sql = "";
		if(ConstantsUtils.isNotBank(ksbh)){
			sql = "select  fhiskh,fhisksname from THISKSSET where FKSTYPE=81 AND FTYKH= '"+ksbh+"'  ";
		}else{
			sql = "select fhiskh,fhisksname from THISKSSET where FKSTYPE=81 ";
		}
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public PageHelper queryStatistics(Map<String, Object> map)  {
		CallableStatement cs = null;//执行sql存储过程的接口
		final String date1 = (String) map.get("date1");
		final String date2 = (String) map.get("date2");
		String cydept = (String) map.get("fcydept");
		final String rqlx = (String) map.get("rqlx");
		final String jsday = (String) map.get("jsday");
		final String yqday = (String) map.get("yqday");
		String page = (String) map.get("page");
		String rows = (String) map.get("rows");
		int pageSize = 10;
		int currentPage = 1;
		 String departNumSql = "select fcal from tworkroom where ftykh='" + cydept + "'";
            Map<String, Object> departNumMap = this.jdbcTemplate.queryForMap(departNumSql);
             final String fcydept = departNumMap.get("fcal").toString().replace(",", "','");
		if (ConstantsUtils.isNotBank(page)) {
            currentPage = Integer.valueOf(page);
        }
        if (ConstantsUtils.isNotBank(rows)) {
            pageSize = Integer.valueOf(rows);
        }
        //调用存储过程
		this.jdbcTemplate.execute(new CallableStatementCreator(){
			@Override
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				String callProc = "{call statisticsArchiving(?,?,?,?,?,?) }";
				CallableStatement cs = con.prepareCall(callProc);
				cs.setString(1, date1);
				cs.setString(2, date2);
				cs.setString(3, fcydept);
				cs.setString(4, jsday);
				cs.setString(5, yqday);
				cs.setString(6, rqlx);
				cs.execute();
				return cs;
			}
			
		}, new CallableStatementCallback(){
			@Override
			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				return null;
			}
			
		});
		String field = " fcytykh,fcydept,total,jsgd,jsgdl,asgd,asgdl,yqgd,yqgdl,wgd,wgdl ";
		String table = " szh_statistics ";
		return this.queryMssqlSplitPageByProc(field, table, "", " fcytykh ", 1, " fcytykh ", currentPage, pageSize);
	}

	@Override
	public PageHelper getYqgdData(Map<String, Object> map) {
		String fcytykh = (String) map.get("fcytykh");
		Integer jsday = (Integer) map.get("jsday");
		Integer yqday = (Integer) map.get("yqday");
		boolean yqgd = (Boolean) map.get("yqgd");
		boolean wgd = (Boolean) map.get("wgd");
		String page = (String) map.get("page");
		String rows = (String) map.get("rows");
		int pageSize = 10;
		int currentPage = 1;
		StringBuffer sb = new StringBuffer(" ");
		String field = "";
		String table = "";
		String orderby = "";
		//yqgd为true，则执行逾期归档查询
		if(yqgd){
			table = "";
			field = "";
			orderby = "";
			sb.append(" a.fstatus = '1' ");
			if(ConstantsUtils.isNotBank(fcytykh)){
				sb.append(" and b.fcytykh = '"+fcytykh+"'");
			}
			if(jsday != null){
				sb.append(" and (DATEDIFF(day, b.fcydate, a.fdate) - ( select count(fid) from TPLAYTIME where fplaydate BETWEEN  b.fcydate and  a.fdate)) >"+yqday+"");
			}
			 field = " a.fzyid,a.fdate,b.fprn,b.fcydept,b.fcytykh,b.fname,b.fcydate,(DATEDIFF(day, b.fcydate, a.fdate) "
					+ "- ( select count(fid) from TPLAYTIME where fplaydate BETWEEN  b.fcydate and  a.fdate) - "+yqday+") as days ";
			 table = " szh_bagd a LEFT JOIN szh_base b on a.fzyid = b.fzyid  ";
			 orderby = "a.fdate";
			 
		}else{
			table = "";
			field = "";
			orderby = "";
			sb.append("  b.fstatus = '0' ");
			sb.append(" or a.fzyid not in(select fzyid from SZH_BAGD) ");
			if(ConstantsUtils.isNotBank(fcytykh)){
				sb.append(" and a.fcytykh = '"+fcytykh+"'");
			}
			field = "a.fzyid,a.fprn,a.fcydept,a.fcytykh,a.fname,a.fcydate";
			table = " szh_base a LEFT JOIN szh_bagd b on a.fzyid = b.fzyid ";
			orderby = "a.fcydate";
		}
		if (ConstantsUtils.isNotBank(page)) {
            currentPage = Integer.valueOf(page);
        }
        if (ConstantsUtils.isNotBank(rows)) {
            pageSize = Integer.valueOf(rows);
        }
		return this.queryMssqlSplitPageByProc(field, table, sb.toString(), orderby, 1, "a.fzyid", currentPage, pageSize);
	}

	@Override
	public PageHelper queryBrowseHistory(Map<String, Object> map) {
		String date1 = (String) map.get("date1");
		String date2 = (String) map.get("date2");
		String fcydept = (String) map.get("fcydept");
		String fzzDoc = (String) map.get("fzzDoc");
		String fprn = (String) map.get("fprn");
		String fname = (String) map.get("fname");
		String jbType = (String) map.get("jbType");
		String jbName = (String) map.get("jbName");
		String oType = (String) map.get("oType");
		String oName = (String) map.get("oName");
		String time = (String) map.get("time");
		String mark = (String) map.get("mark");
		String page = (String) map.get("page");
		String rows = (String) map.get("rows");
		StringBuffer sb = new StringBuffer(" 1=1 ");
		int pageSize = 10;
		int currentPage = 1;
		String Time = "";
		if(ConstantsUtils.isNotBank(date1)){
			sb.append(" and x.fcydate >= '"+date1+"' ");
		}
		if(ConstantsUtils.isNotBank(date2)){
			Date DATE = new Date();
			try {
				DATE = sdf.parse(date2);
			} catch (ParseException e) {
				e.printStackTrace();
			}
    		 Date date = new Date(DATE.getTime() + 1000 * 60 * 60 * 24);
    		 String Date = sdf.format(date);
			sb.append(" and x.fcydate < '"+Date+"'");
		}
		if(ConstantsUtils.isNotBank(fzzDoc)){
			sb.append(" and x.fzzdoctbh = '"+fzzDoc+"'");
		}
		if(ConstantsUtils.isNotBank(fprn)){
			sb.append(" and x.fprn = '"+fprn+"' or x.fbarcode = '"+fprn+"'");
		}
		if(ConstantsUtils.isNotBank(fname)){
			sb.append(" and x.fname like '%"+fname+"%'");
		}
		if(ConstantsUtils.isNotBank(jbType)){
			if(jbType.equals("0")){ //全部
				sb.append("");
			}else if(jbType.equals("1")){ //主要诊断
				sb.append(" and x.fzdlx = '1' ");
			}else if(jbType.equals("2")){ //其他
				sb.append(" and x.fzdlx = '2'");
			}else if(jbType.equals("2")){ //损伤
				sb.append(" and x.fzdlx = 's'");
			}
		}
		if(ConstantsUtils.isNotBank(jbName)){
			sb.append(" and x.fjbname like '%"+jbName+"%'");
		}
		if(ConstantsUtils.isNotBank(oType)){
			if(oType.equals("0")){ //全部
				sb.append("");
			}else if(oType.equals("1")){ //主要
				sb.append(" and x.fiffsop = '0'");
			}else if(oType.equals("2")){ //其他
				sb.append(" and x.fiffsop = '1'");
			}
		}
		if(ConstantsUtils.isNotBank(oName)){
			sb.append(" and x.fop like '%"+oName+"%'");
		}
		if(ConstantsUtils.isNotBank(time)){
			if(time.equals("all")){
				Time += "";
			}else if(time.equals("today")){ //今天
				Time += " and CONVERT(VARCHAR(10),a.fdate,120)=  CONVERT(VARCHAR(10),GETDATE(),120) ";
			}else if(time.equals("thisWeek")){ //本周
				Time +=" and datediff(week,a.fdate,getdate())=0 ";
			}else if(time.equals("lastWeek")){ //上周
				Time +=" and datediff(week,a.fdate,getdate())=1";
			}else if(time.equals("year")){ //年份
				Time +=" and DATENAME(YEAR,a.fdate) = '"+mark+"'";
			}else if(time.equals("month")){ //月份
				Time += " and DATENAME(month,a.fdate) = '"+mark+"'";
			}
		}
		if(ConstantsUtils.isNotBank(fcydept)){
			 String departNumSql = "select fcal from tworkroom where ftykh='" + fcydept + "'";
	            Map<String, Object> departNumMap = this.jdbcTemplate.queryForMap(departNumSql);
	            String departNumStr = departNumMap.get("fcal").toString().replace(",", "','");
	            sb.append(" and x.fcytykh in ('" + departNumStr + "')");
		}
		if (ConstantsUtils.isNotBank(page)) {
			currentPage = Integer.valueOf(page);
		}
		if (ConstantsUtils.isNotBank(rows)) {
			pageSize = Integer.valueOf(rows);
		}
		//String field = " DISTINCT a.fzyid,d.fop,c1.fjbname,b.fprn,b.ftimes,b.fname,b.fsex,b.fage,b.fcydate,b.fbarcode,b.frydate,b.fzzdoctbh,b.fzzdoct,b.fcydept ";
		String field = " x.* ";
		String table = " ( SELECT DISTINCT a.fzyid, d.fop, c1.fjbname, b.fprn, b.ftimes, b.fname, b.fsex, b.fage,"
				+ "b.fcydate, b.fbarcode, b.frydate, b.fzzdoctbh, b.fzzdoct, b.fcydept,b.fcytykh, c1.fzdlx,e.fstatus FROM szh_base b  "
				+ " RIGHT JOIN szh_browserecord  a on b.fzyid = a.fzyid "
				+ " LEFT JOIN toperation d  on  b.fprn = d.fprn and b.ftimes = d.ftimes "
				+ " LEFT JOIN TDIAGNOSE c ON b.fprn=c.fprn and b.ftimes=c.ftimes "
				+ " LEFT JOIN TDIAGNOSE c1 ON b.fprn=c1.fprn and b.ftimes=c1.ftimes "
				+ "	LEFT JOIN SZH_SHOOTRECORD e on a.fzyid = e.fzyid where c1.fzdlx='1' "+Time+" )x";
		return this.queryMssqlSplitPageByProc(field, table, sb.toString(), "x.fzyid", 1, "x.fzyid", currentPage, pageSize);
	}

	@Override
	public Map<String, Object> getToday() {
		String sql = "SELECT CONVERT(VARCHAR(10),GETDATE(),120)as fdate  from szh_browserecord";
		return this.jdbcTemplate.queryForMap(sql);
	}

	@Override
	public Map<String, Object> getThisWeek() {
		String sql = "SELECT fdate FROM szh_browserecord WHERE datediff(week,fdate,getdate())=0 "
				+ "and fdate NOT in(SELECT CONVERT(VARCHAR(10),GETDATE(),120)as fdate  from szh_browserecord)";
		return this.jdbcTemplate.queryForMap(sql);
	}

	@Override
	public Map<String, Object> getLastWeek() {
		String sql = "SELECT fdate FROM szh_browserecord where  datediff(week,fdate,getdate())=1";
		return this.jdbcTemplate.queryForMap(sql);
	}

	@Override
	public List<Map<String, Object>> getAllYear() {
		String sql = "select YEAR(fdate)as fyear,MONTH(fdate)as fmonth from szh_browserecord GROUP BY YEAR(fdate),MONTH(fdate) ORDER BY MONTH(fdate)";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
		return list;
	}

	@Override
	public List<Map<String, Object>> getAllBrowseName() {
		String sql = "select DISTINCT fusername,fusercode from SZH_BROWSERECORD";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
		return list;
	}

	@Override
	public PageHelper queryBrowseLog(Map<String, Object> map) {
		String date1 = (String) map.get("date1");
		String date2 = (String) map.get("date2");
		String fcydept = (String) map.get("fcydept");
		String browseName = (String) map.get("browseName");
		String type = (String) map.get("type");
		String page = (String) map.get("page");
		String rows = (String) map.get("rows");
		StringBuffer sb = new StringBuffer(" 1=1 ");
		int pageSize = 10;
		int currentPage = 1;
		if(ConstantsUtils.isNotBank(date1)){
			sb.append(" and b.fcydate >= '"+date1+"' ");
		}
		if(ConstantsUtils.isNotBank(date2)){
			Date DATE = new Date();
			try {
				DATE = sdf.parse(date2);
			} catch (ParseException e) {
				e.printStackTrace();
			}
    		 Date date = new Date(DATE.getTime() + 1000 * 60 * 60 * 24);
    		 String Date = sdf.format(date);
			sb.append(" and x.fcydate < '"+Date+"'");
		}
		if(ConstantsUtils.isNotBank(browseName)){
			sb.append(" and a.fusercode like '%"+browseName+"%'");
		}
		if(ConstantsUtils.isNotBank(type)){ 
			if(type.equals("0")){ //0 出院科室
				String departNumSql = "select fcal from tworkroom where ftykh='" + fcydept + "'";
	            Map<String, Object> departNumMap = this.jdbcTemplate.queryForMap(departNumSql);
	            String departNumStr = departNumMap.get("fcal").toString().replace(",", "','");
	            sb.append(" and b.fcytykh in ('" + departNumStr + "')");
			}else{ //1 登录科室
				
			}
		}
		if (ConstantsUtils.isNotBank(page)) {
			currentPage = Integer.valueOf(page);
		}
		if (ConstantsUtils.isNotBank(rows)) {
			pageSize = Integer.valueOf(rows);
		}
		String field = " a.*,b.fprn,b.ftimes,b.fname,b.fsex,b.fcydate,b.fcydept,b.fbarcode,c.fdate ";
		String table = " (select a.fzyid,a.fusername,a.fusercode,a.ip from szh_browserecord a group by a.fzyid,a.fusername,a.fusercode,a.ip)a "
				+ " LEFT JOIN szh_base b on a.fzyid = b.fzyid  "
				+ " LEFT JOIN (select max(fdate)as fdate,fzyid from  SZH_BROWSERECORD GROUP BY fzyid) c on a.fzyid = c.fzyid ";
		return this.queryMssqlSplitPageByProc(field, table, sb.toString(), "c.fdate", 1, "a.fzyid", currentPage, pageSize);
	}

	@Override
	public PageHelper getApplyRecord(Map<String, Object> map) {
		  String date1 = (String) map.get("date1");
		  String date2 = (String) map.get("date2");
		  String fcydept = (String) map.get("fcydept");
		  String fzzDoc = (String) map.get("fzzDoc");
		  String fprn = (String) map.get("fprn");
		  String fname = (String) map.get("fname");
		  String type = (String) map.get("type");
		  String status = (String) map.get("status");
		  String purpose = (String) map.get("purpose");
		  String page = (String) map.get("page");
		  String rows = (String) map.get("rows");
		  int pageSize = 10;
		  int currentPage = 1;
		  StringBuffer sb = new StringBuffer(" 1=1 ");
		  if(ConstantsUtils.isNotBank(fzzDoc)){
				sb.append(" and b.FZZDOCTBH = '"+ fzzDoc +"' ");
		  }
		  if(ConstantsUtils.isNotBank(fprn)){
				sb.append(" and b.fprn = '"+fprn+"' or b.fbarcode = '"+fprn+"' ");
		  }
		  if(ConstantsUtils.isNotBank(fname)){
				sb.append(" and b.fname like '%"+ fname +"'%");
		  }
		  if(ConstantsUtils.isNotBank(type)){
			  if(type.equals("0")){ //申请日期
				  if(ConstantsUtils.isNotBank(date1)){
					  sb.append(" and a.fsqrq >= '"+date1+"'");
				  }else if(ConstantsUtils.isNotBank(date2)){
					  Date DATE = new Date();
						try {
							DATE = sdf.parse(date2);
						} catch (ParseException e) {
							e.printStackTrace();
						}
			    		 Date date = new Date(DATE.getTime() + 1000 * 60 * 60 * 24);
			    		 String Date = sdf.format(date);
						sb.append(" and  a.fsqrq < '"+Date+"'");
				  }
			  }else{  //出院日期
				  if(ConstantsUtils.isNotBank(date1)){
						sb.append(" and  b.fcydate >= '"+date1+"' ");
				  }else if(ConstantsUtils.isNotBank(date2)){
						Date DATE = new Date();
						try {
							DATE = sdf.parse(date2);
						} catch (ParseException e) {
							e.printStackTrace();
						}
			    		 Date date = new Date(DATE.getTime() + 1000 * 60 * 60 * 24);
			    		 String Date = sdf.format(date);
						sb.append(" and  b.fcydate < '"+Date+"'");
					  }  
			    }
		  }
		  if(ConstantsUtils.isNotBank(status)){	//审批状态
			  
		  }
		  if(ConstantsUtils.isNotBank(purpose)){	//申请目的
			  sb.append(" and a.fsqmd = '"+purpose+"'");
		  }
		  if (ConstantsUtils.isNotBank(page)) {
				currentPage = Integer.valueOf(page);
		  }
		  if (ConstantsUtils.isNotBank(rows)) {
			pageSize = Integer.valueOf(rows);
		  }
		  String field = " a.fid,a.fzyid,a.fsqrq,a.fsqdays,a.fsqmd,b.fprn,b.ftimes,b.fname,b.fcydate,b.fsex,b.fage,b.fcydept,"
		  				+ " b.fzzdoct,b.fzzdoctbh,b.fbarcode,c.fspzt,c.fdqlc,c.fqsrq,c.fjsrq ";
		  String table = " szh_dgsq a left join szh_base b on a.fzyid = b.fzyid left join szh_dgsqjl c on a.fid = b.fid ";
		return this.queryMssqlSplitPageByProc(field, table, sb.toString(), "a.fsqrq", 1, "a.fzyid",  currentPage, pageSize);
	}

	@Override
	public List<Map<String, Object>> getApplyDetails(String fzyid) {
		String sql = "SELECT b.fspzt,b.fdqlc,b.fspy,b.fqsrq,b.fjsrq,b.fspms from "
				+ "SZH_DGSQ a LEFT JOIN SZH_DGSQJL b on a.fid = b.FSQID where a.fid = "+fzyid+"";
		List<Map<String, Object>> list  = this.jdbcTemplate.queryForList(sql);
		return list;
	}

	@Override
	public PageHelper getBatchApplyRecord(Map<String, Object> map) {
		String date1 = (String) map.get("date1");
		String date2 = (String) map.get("date2");
		String purpose = (String) map.get("purpose");
		String status = (String) map.get("status");
		String page = (String) map.get("page");
		String rows = (String) map.get("rows");
		StringBuffer sb = new StringBuffer(" 1=1 ");
		int pageSize = 10;
		int currentPage = 1;
		 if(ConstantsUtils.isNotBank(date1)){
				sb.append(" and  a.fsqrq >= '"+date1+"' ");
		  }
		 if(ConstantsUtils.isNotBank(date2)){
				Date DATE = new Date();
				try {
					DATE = sdf.parse(date2);
				} catch (ParseException e) {
					e.printStackTrace();
				}
	    		 Date date = new Date(DATE.getTime() + 1000 * 60 * 60 * 24);
	    		 String Date = sdf.format(date);
				sb.append(" and  a.fsqrq < '"+Date+"'");
		}  
		 if(ConstantsUtils.isNotBank(status)){	//审批状态
			 if(status.equals("3")){
				 sb.append(" ");
			 }else{
				 sb.append(" and a.fshztbh = '"+ status +"'");
			 }
		 }
		 if(ConstantsUtils.isNotBank(purpose)){	//申请目的
			 if(purpose.equals("0")){
				 sb.append(" ");
			 }else{
				 sb.append(" and c.fid = "+ purpose +"");
			 }
	     }
		 if (ConstantsUtils.isNotBank(page)) {
				currentPage = Integer.valueOf(page);
		  }
		  if (ConstantsUtils.isNotBank(rows)) {
			pageSize = Integer.valueOf(rows);
		  }
		 String field = " b.fsqid,a.fshztbh,a.fshzt,b.fdqlc,a.fsqrq,a.fsqdays,a.fsqtjwz,b.fqsrq,b.fjsrq,c.fname,c.fid  ";
		 String table = " szh_plsq a left join (select max(fsprq) as fsprq,fqsrq,fjsrq,fdqlc,fsqid from szh_plsqjl  "
		 		+ " group by fqsrq,fjsrq,fdqlc,fsqid) b  on a.fid = b.fsqid left join  szh_jymd c on a.fsqmd = c.fid ";
		return this.queryMssqlSplitPageByProc(field, table, sb.toString(), "a.fsqrq", 1, "b.fsqid", currentPage, pageSize);
	}

	@Override
	public List<Map<String, Object>> getBatchApplyDetails(String fsqid) {
		String sql = "  select b.fspzt,b.fdqlc,b.fspy,b.fsprq,a.fremark from szh_plsq a "
				+ "LEFT JOIN SZH_PLSQJL b  on a.FID = b.fsqid where a.fid = "+fsqid+"";
		List<Map<String, Object>> list  = this.jdbcTemplate.queryForList(sql);
		return list;
	}

	@Override
	public PageHelper getBatchList(Map<String, Object> map) {
		String fsqid = (String) map.get("fsqid");
		String page = (String) map.get("page");
		String rows = (String) map.get("rows");
		StringBuffer sb = new StringBuffer(" 1=1 ");
		int pageSize = 10;
		int currentPage = 1;
		if(ConstantsUtils.isBank(fsqid)){
			return null;
		}
		String sql = " select a.fsqtjsql from szh_plsq a where a.fid = "+fsqid+"";
		Map<String, Object> sqlMap = this.jdbcTemplate.queryForMap(sql);
		if (ConstantsUtils.isNotBank(page)) {
				currentPage = Integer.valueOf(page);
		 }
		if (ConstantsUtils.isNotBank(rows)) {
			pageSize = Integer.valueOf(rows);
		 }
		 String field = " x.*";
		/* String table = " (SELECT DISTINCT CASE WHEN t.fzyid IN ( SELECT fzyid FROM SZH_SHOOTRECORD "
		 		+ " ) THEN 1 WHEN t.fzyid IN ( SELECT fzyid FROM szh_dgsq WHERE fusercode = 'szh' "
		 		+ " ) THEN 2 ELSE 0 END fzt, t.* FROM szh_base t INNER JOIN TDIAGNOSE a ON t.fprn = a.fprn "
		 		+ " AND t.ftimes = a.ftimes INNER JOIN TOPERATION b ON t.fprn = b.fprn AND t.ftimes = b.ftimes "
		 		+ " WHERE 1 = 1 AND t.fcydate >= '2015-03-10' AND t.fcydate <= '2017-06-16' "
		 		+ " AND a.fzdlx IN ('1', '2', 's', '11', '12', '13') ) x  ";*/
		String table = "(" + (String) sqlMap.get("fsqtjsql") + ") x";
		return this.queryMssqlSplitPageByProc(field, table, sb.toString(), "x.FZYID", 1, "x.FZYID", currentPage, pageSize);
	}

	@Override
	public List<Map<String, Object>> getNotArchived()throws Exception {
		//只查一天数据量
		String sql="SELECT S.FZYID,S.FPRN,S.FTIMES,S.FNAME,G.FID FROM dbo.SZH_BASE S LEFT JOIN dbo.SZH_BAGD G ON S.FPRN=G.FPRN WHERE (G.FSTATUS=0 OR G.FSTATUS IS NULL) AND DATEADD(DAY,-1,getdate())<S.FCYDATE AND S.FCYDATE<DATEADD(second,0,CONVERT(DATETIME,GETDATE()))";
		List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
		return list;
	}

	@Override
	public void updateArchivedStatus(String fid) throws Exception {
		String sql="UPDATE dbo.SZH_BAGD SET FSTATUS=1,FDATE=GETDATE() WHERE FID="+fid;
		this.jdbcTemplate.execute(sql);
	}

	@Override
	public void addArchived(String fzyid, String fprn, String ftimes,
			String fname) throws Exception {
		String sql="INSERT INTO dbo.SZH_BAGD (FZYID,FPRN,FTIMES,FNAME,FDATE,FSTATUS) VALUES ('"+fzyid+"','"+fprn+"',"+ftimes+",'"+fname+"',GETDATE(),1)";
		this.jdbcTemplate.execute(sql);
	}

	@Override
	public void addArchived(String fprn, String ftimes,String status) throws Exception {
		String sql="if exists(select 1 from dbo.SZH_BAGD where FPRN='"+fprn+"' AND FTIMES="+ftimes+") "+
				" update dbo.SZH_BAGD set FSTATUS="+status+",FDATE=GETDATE() where FPRN='"+fprn+"' AND FTIMES="+ftimes+
				" else "+
				" insert into dbo.SZH_BAGD (FZYID,FPRN,FTIMES,FNAME,FDATE,FSTATUS,FFILETYPE) SELECT s.FZYID,s.fprn,s.ftimes,s.fname,GETDATE(),"+status+",'PDF' FROM dbo.SZH_BASE s WHERE s.fprn='"+fprn+"' AND s.ftimes="+ftimes;
		
		this.jdbcTemplate.execute(sql);
	}

	@Override
	public void addBagdmx(String fprn, String ftimes, String fblmc,
			String fbllx, String fpages, String ftemppath) throws Exception {
		String sql="select max(fid) id from dbo.SZH_BAGD where FPRN='"+fprn+"' AND FTIMES="+ftimes;
		List<Map<String,Object>> list=this.jdbcTemplate.queryForList(sql);
		if(list.size()>0){//有归档记录
			sql="if exists(select 1 from dbo.SZH_BAGDMX where FBLMC='"+fblmc.trim()+"' AND FGDID="+list.get(0).get("id")+") "+
				" update dbo.SZH_BAGDMX set FBLLX="+fbllx+",FTEMPDATE=GETDATE(),FPAGES="+fpages+",FTEMPPATH='"+ftemppath+"' where FBLMC='"+fblmc.trim()+"' AND FGDID="+list.get(0).get("id")+
				" else "+
				" insert into dbo.SZH_BAGDMX (FGDID,FBLMC,FBLLX,FPAGES,FTEMPPATH,FTEMPDATE) values ("+list.get(0).get("id")+",'"+fblmc+"','"+fbllx+"',"+fpages+",'"+ftemppath+"',GETDATE())";
			this.jdbcTemplate.execute(sql);
		}
		
	}

	@Override
	public void updateBagdmx(String fprn, String ftimes, String fblmc,
			String encryptpath, String signpath) throws Exception {
		String sql="select max(fid) id from dbo.SZH_BAGD where FPRN='"+fprn+"' AND FTIMES="+ftimes;
		List<Map<String,Object>> list=this.jdbcTemplate.queryForList(sql);
		if(list.size()>0){//有归档记录
			sql="UPDATE dbo.SZH_BAGDMX SET FFTPDATE=GETDATE(),FFTPSIGNPATH='"+signpath+"',FFTPENCRYPTPATH='"+encryptpath+"' WHERE FBLMC='"+fblmc.trim()+"' AND FGDID="+list.get(0).get("id");
			this.jdbcTemplate.execute(sql);
		}
	}

	@Override
	public void saveArchived(Map<String, Object> param) throws Exception {
		String sql="if exists(select 1 from dbo.SZH_BAGD where (FPRN='"+param.get("prn")+"' AND FTIMES="+param.get("times")+") OR FZYID='"+param.get("zyid")+"') "+
				" update dbo.SZH_BAGD set FSTATUS="+1+",FDATE=GETDATE() where (FPRN='"+param.get("prn")+"' AND FTIMES="+param.get("times")+") OR FZYID='"+param.get("zyid")+"'"+
				" else "+
				" insert into dbo.SZH_BAGD (FZYID,FPRN,FTIMES,FNAME,FDATE,FSTATUS,FFILETYPE) SELECT s.FZYID,s.fprn,s.ftimes,s.fname,GETDATE(),"+1+",'PDF' FROM dbo.SZH_BASE s WHERE (s.fprn='"+param.get("prn")+"' AND s.ftimes="+param.get("times")+") OR FZYID='"+param.get("zyid")+"'";
		this.jdbcTemplate.execute(sql);
		sql="select * from dbo.SZH_BLLXMX where FNAME='"+(param.get("filename")+"").trim()+"'";
		List<Map<String,Object>> blmxList=this.jdbcTemplate.queryForList(sql);
		Map<String,Object> bllxmx=null;
		String FBLLX="-";
		String BLCODE="";
		if(blmxList.size()>0){//有病历明细
			bllxmx=blmxList.get(0);
			FBLLX=bllxmx.get("FTYPE")+"";
			BLCODE=bllxmx.get("BLCODE")+"";
		}
		if(!GlobalFun.hasValue(BLCODE)){
			throw new Exception("系统未设置病历类型。");
		}
	    sql="select max(fid) id from dbo.SZH_BAGD where (FPRN='"+param.get("prn")+"' AND FTIMES="+param.get("times")+") OR FZYID='"+param.get("zyid")+"'";
	    List<Map<String,Object>> list=this.jdbcTemplate.queryForList(sql);
		if(list.size()>0){//有归档记录
			if(!GlobalFun.hasValue(list.get(0).get("id"))){
				throw new Exception("系统中不存在此病人信息，无法归档。");
			}
			sql="if exists(select 1 from dbo.SZH_BAGDMX where FBLMC='"+(param.get("filename")+"").trim()+"' AND FGDID="+list.get(0).get("id")+") "+
				" update dbo.SZH_BAGDMX set FBLLX='"+(param.get("filename")+"").trim()+"',FENCRYPTDATE=GETDATE(),FPAGES=0,FENCRYPTPATH='"+param.get("encryptFile")+"' where FBLMC='"+(param.get("filename")+"").trim()+"' AND FGDID="+list.get(0).get("id")+
				" else "+
				" insert into dbo.SZH_BAGDMX (FGDID,FBLMC,FBLLX,FPAGES,FENCRYPTPATH,FENCRYPTDATE,FBLCODE) values ("+list.get(0).get("id")+",'"+(param.get("filename")+"").trim()+"','"+FBLLX+"',0,'"+param.get("encryptFile")+"',GETDATE(),'"+BLCODE+"')";
			this.jdbcTemplate.execute(sql);
		}
	}


}
